let mysql = require('mysql')
let config = {
	  timezone:"SYSTEM",
	  connectionLimit : 10,
	  connectTimeout  : 10 * 1000,
	  acquireTimeout  : 10 * 1000,
	  timeout         : 10 * 1000,
	  host: 'localhost',
	  //host: '10.32.0.1',
	  user: 'root',
	  password: 'Abc123!!!',
	  database: 'ei'
	}

let pool  = mysql.createPool(config);

let trans_connection = null;
let in_trans = false;

let is_in_trans=function(){
	return in_trans;
}

let transaction=function(multi){
	return new Promise(function(resolve, reject){
		trans_connection = mysql.createConnection({
			...config,
			multipleStatements:multi
		});
		trans_connection.connect();
		trans_connection.beginTransaction(function(err) {
			if(err){reject(err);return;}
			in_trans=true;
			resolve({
				add: add,
				del: del,
				update: update,
				query: query,
				commit: commit,
				rollback: rollback,
				multiadd: multiadd,
				multiquery: multiquery,
				multidel: multidel,
				multiupdate: multiupdate,
				queryfilter: queryfilter
			});
		});
	});
}

let commit=function(){
	return new Promise(function(resolve, reject){
		trans_connection.commit(function(err) {
			if (err) {
				reject(err);
			}
			else{
				in_trans=false;
				trans_connection.end();
				resolve();
				console.log('Transaction commit!');
			}
		});
	});
}

let rollback=function(){
	return new Promise(function(resolve, reject){
		trans_connection.rollback(function(){
			in_trans=false;
			trans_connection.end();
			resolve();
			console.warn('Transaction rollback!');
		});
	});
}

let add=function(sql,params){
	return new Promise(function(resolve, reject){
		console.log(sql,params);
		(in_trans?trans_connection:pool).query(sql, params, function (err, results, fields) {
			if(err) reject(err);
			else {
				console.log('insertId:'+(results.insertId?results.insertId:results[0].insertId))
				resolve({results:results});
			}
		});
	});
}

let del=function(sql,params){
	return new Promise(function(resolve, reject){
		console.log(sql,params);
		(in_trans?trans_connection:pool).query(sql, params, function (err, results, fields) {
			if(err) reject(err);
			else {
				console.log('deleted:'+(results.affectedRows||results.affectedRows==0?results.affectedRows:results[0].affectedRows))
				resolve({results:results});
			}
		});
	});
}

let update=function(sql,params){
	return new Promise(function(resolve, reject){
		console.log(sql,params);
		(in_trans?trans_connection:pool).query(sql, params, function (err, results, fields) {
			if(err) reject(err);
			else{
				console.log('affectedRows:'+(results.affectedRows||results.affectedRows==0?results.affectedRows:results[0].affectedRows))
				resolve({results:results});
			} 
		});
	});
}

let query=function(sql,params){
	return new Promise(function(resolve, reject){
		console.log(sql,params);
		(in_trans?trans_connection:pool).query(sql, params, function (err, results, fields) {
			if(err) reject(err);
			else {
				console.log('query rows:'+(results?(results[0]&&results[0].length?results[0].length:results.length):0))
				resolve({rows:results});
			}
		});
	});
}

let multiadd=function(sql,params){
	return new Promise(function(resolve, reject){
		var connection = mysql.createConnection({
		  ...config,
		  multipleStatements: true
		});
		connection.connect();
		console.log(sql,params);
		(in_trans?trans_connection:connection).query(sql, params, function (err, results, fields) {
			if(err) {
				connection.end();
				reject(err);
			}
			else {
				console.log('insertId:'+(results.insertId?results.insertId:results[0].insertId))
				connection.end();
				resolve({results:results});
			}
		});
	});
}

let multidel=function(sql,params){
	return new Promise(function(resolve, reject){
		var connection = mysql.createConnection({
		  ...config,
		  multipleStatements: true
		});
		connection.connect();
		console.log(sql,params);
		(in_trans?trans_connection:connection).query(sql, params, function (err, results, fields) {
			if(err) {
				connection.end();
				reject(err);
			}
			else {
				console.log('deleted:'+(results.affectedRows||results.affectedRows==0?results.affectedRows:results[0].affectedRows))
				connection.end();
				resolve({results:results});
			}
		});
	});
}

let multiupdate=function(sql,params){
	return new Promise(function(resolve, reject){
		var connection = mysql.createConnection({
		  ...config,
		  multipleStatements: true
		});
		connection.connect();
		console.log(sql,params);
		(in_trans?trans_connection:connection).query(sql, params, function (err, results, fields) {
			if(err) {
				connection.end();
				reject(err);
			}
			else{
				console.log('affectedRows:'+(results.affectedRows||results.affectedRows==0?results.affectedRows:results[0].affectedRows))
				connection.end();
				resolve({results:results});
			}
		});
	});
}

let multiquery=function(sql,params){
	return new Promise(function(resolve, reject){
		var connection = mysql.createConnection({
		  ...config,
		  multipleStatements: true
		});
		connection.connect();
		console.log(sql,params);
		(in_trans?trans_connection:connection).query(sql, params, function (err, results, fields) {
			if(err) {
				connection.end();
				reject(err);
			}
			else {
				console.log('query rows:'+(results?(results[0]&&results[0].length?results[0].length:results.length):0))
				connection.end();
				resolve({rows:results});
			}
		});
	});
}

//处理复合条件的查询条件为SQLf
let handle_composite_filter=function(f){
	if(f.conjunction && f.children){ //复合条件
		let children=[];
		for(let child of f.children){
			let result=handle_composite_filter(child);
			if(result){
				children.push("("+result+")");
			}
		}
		return children.join(" "+f.conjunction+" ");
	}
	else if(f.left && f.right && f.op){
		if(f.op=="equal" && f.right){
			return (f.prefix?f.prefix+".":"")+pool.escapeId(f.left.field)+"="+pool.escape(f.right);
		}
		else if(f.op=="not_equal" && f.right){
			return (f.prefix?f.prefix+".":"")+pool.escapeId(f.left.field)+"<>"+pool.escape(f.right)+" or "+(f.prefix?f.prefix+".":"")+pool.escapeId(f.left.field)+" is null";
		}
		else if(f.op=="like" && f.right){
			return (f.prefix?f.prefix+".":"")+pool.escapeId(f.left.field)+" like "+pool.escape('%'+f.right+'%');
		}
		else if(f.op=="not_like" && f.right){
			return (f.prefix?f.prefix+".":"")+pool.escapeId(f.left.field)+" not like "+pool.escape('%'+f.right+'%')+" or "+(f.prefix?f.prefix+".":"")+pool.escapeId(f.left.field)+" is null";
		}
		else if(f.op=="less" && f.right){
			return (f.prefix?f.prefix+".":"")+pool.escapeId(f.left.field)+"<"+pool.escape(f.right);
		}
		else if(f.op=="greater" && f.right){
			return (f.prefix?f.prefix+".":"")+pool.escapeId(f.left.field)+">"+pool.escape(f.right);
		}
		else if(f.op=="between" && f.right.length==2){
			return (f.prefix?f.prefix+".":"")+pool.escapeId(f.left.field)+">"+pool.escape(f.right[0])+" and "+(f.prefix?f.prefix+".":"")+pool.escapeId(f.left.field)+"<"+pool.escape(f.right[1]);
		}
	}
	return;
}

let queryfilter=function(sql_start,params,searchdata){
	return new Promise(function(resolve, reject){
		let page=searchdata.page;
		let perpage=searchdata.perpage;
		let orderby=searchdata.orderby;
		let orderby_prefix=searchdata.orderby_prefix;
		let orderdir=searchdata.orderdir;
		let keywords=searchdata.keywords;
		let keywords_search_columns=searchdata.keywords_search_columns;
		let filter=searchdata.filter;
		let limit=searchdata.limit;
		let where="where 1=1";
		if(filter){
			for(let f of filter){
				let prefix=f.prefix;
				let key=f.key;
				let value=f.value;
				let like=f.like;
				let not=f.not;
				if(f.conjunction && f.children){ //复合条件
					let result=handle_composite_filter(f);
					if(result){
						where+=" and ("+result+") ";
					}
				}
				else{
					if(!key){ //简单过滤
						continue;
					}
					if(!value){ //value是空值，则不考虑like，只查是否是空值
						if(!not){
							where+=" and ( "+(prefix?prefix+".":"")+pool.escapeId(key)+"=''"
							+" or "+(prefix?prefix+".":"")+pool.escapeId(key)+" is null )";
						}
						else{
							where+=" and ( "+(prefix?prefix+".":"")+pool.escapeId(key)+"<>''"
							+" and "+(prefix?prefix+".":"")+pool.escapeId(key)+" is not null )";
						}
					}
					else if(like){
						if(!not){
							where+=" and "+(prefix?prefix+".":"")+pool.escapeId(key)+" like "+pool.escape('%'+value+'%');
						}
						else{
							where+=" and ( "+(prefix?prefix+".":"")+pool.escapeId(key)+" not like "+pool.escape('%'+value+'%')
							+" or "+(prefix?prefix+".":"")+pool.escapeId(key)+" is null )";
						}
					}
					else{
						if(!not){
							where+=" and "+(prefix?prefix+".":"")+pool.escapeId(key)+"="+pool.escape(value);
						}
						else{
							where+=" and ( "+(prefix?prefix+".":"")+pool.escapeId(key)+"<>"+pool.escape(value)
							+" or "+(prefix?prefix+".":"")+pool.escapeId(key)+" is null )";
						}
					}
				}
			}
		}
		if(keywords){
			where+=' and (1=0';
			for(let col of keywords_search_columns){
				let prefix=col.prefix;
				where+=" or "+(prefix?prefix+".":"")+pool.escapeId(col.name)+" like "+pool.escape('%'+keywords+'%');
			}
			where+=')'
		}
		let sql=sql_start+" "+where;
		if(orderby){
			let prefix=orderby_prefix;
			sql+=" order by "+(prefix?prefix+".":"")+pool.escapeId(orderby)+(orderdir=="desc"?" desc":"");
		}
		if(page && perpage){
			sql+=' limit '+parseInt((page-1)*perpage)+','+parseInt(perpage);
		}
		else if(limit && limit.from!=undefined && limit.count!=undefined){
			sql+=' limit '+parseInt(limit.from)+","+parseInt(limit.count);
		}
		console.log(sql);
		(in_trans?trans_connection:pool).query(sql, params, function (err, results, fields) {
			if(err) {
				console.log(err);
				reject(Error('操作数据库错误：'+err.message)); 
			}
			else {
				console.log('query rows:'+(results?(results[0]&&results[0].length?results[0].length:results.length):0))
				let count_sql="select count(1) as ct from ("+sql_start+" "+where+") t";
				console.log(count_sql);
				pool.query(count_sql, params, function (err, count_results, fields) {
					if(err) {
						console.log(err);
						reject(Error('操作数据库错误：'+err.message));
					}
					else {
						console.log('query rows count:'+count_results[0].ct)
						resolve({rows:results,count:count_results[0].ct});
					}
				});
			}
		});
	});
}

let escape=function(value){
	return mysql.escape(value);
}

let escapeId=function(id){
	return mysql.escapeId(id);
}

let systemfields=[
	{
		name:"id",
		label:"ID"
	},
	{
		name:"create_time",
		label:"创建时间"
	},
	{
		name:"create_user",
		label:"创建者ID",
		related:{
			"datamodel": "22",
			"model_table_name": "sys_user",
			"that_field": "id",
			"that_label_field": "username",
			"label": "创建者"
		}
	},
	{
		name:"update_time",
		label:"更新时间"
	},
	{
		name:"update_user",
		label:"更新者ID",
		related:{
			"datamodel": "22",
			"model_table_name": "sys_user",
			"that_field": "id",
			"that_label_field": "username",
			"label": "更新者"
		}
	},
	{
		name:"is_deleted",
		label:"已删除",
		hidden: true
	}
]

module.exports={query,add,del,update,multiquery,multiadd,multidel,multiupdate,queryfilter,
	transaction,is_in_trans,escape,escapeId,systemfields,handle_composite_filter}